﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Aden.Model.MenuData;
using Aden.Model.SOMastData;
using Aden.Util.Database;
using Aden.Util.Common;

namespace Aden.DAL.MastData
{
    public class ItemFactory
    {
        public string RecipeCostFrom(string dbName, string costCenterCode)
        {
            string sqlRecipe = "SELECT ISNULL(ISNULL(B.RECIPECOSTFROM,A.RECIPECOSTFROM),'PRICELIST') RECIPECOSTFROM " +
                  " FROM COMPANY (NOLOCK) A LEFT JOIN CCMAST (NOLOCK) B " +
                  " ON A.DBNAME = B.DBName AND ISNULL(B.CostCenterCode,'')='{1}' " +
                  " WHERE A.DBNAME = '{0}'";
            sqlRecipe = string.Format(sqlRecipe, dbName, costCenterCode);
            string recipeCostFrom = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sqlRecipe).ToString();
            return recipeCostFrom;
        }
        
        #region 得到一个公司/成本中心对应的所有采购物料
        public List<Item> getPurchaseItemsFromMDM(string dbName, string costCenterCode, string requiredDate, string type)
        {
            try
            {
                string sql = string.Empty;
                string supplierfilter = string.Empty;
                // 是StdCost还是PriceList
             
                string recipeCostFrom = RecipeCostFrom(dbName, costCenterCode);

                if (type == "recipe")
                {
                    if (recipeCostFrom.ToLower() == "stdcost")
                    {
                        // StdCost
                        sql = "SELECT DISTINCT A.ITEMCODE,B.ITEMNAME_ZH AS ITEMNAME_ZH,CAST(A.PURPRICE AS DECIMAL(18,6)) PRICE, "
                            + "STKBOM_CONVERSION AS CONVERSIONRATE, A.BOM_UNIT AS BOMUNIT,A.STOCK_UNIT AS STOCKUNIT ,'' SupplierCode "
                            + "FROM [dbo].[DM_D_DIGIMENU_STDPURCHASEPRICE] (NOLOCK) A "
                            + "LEFT JOIN [dbo].[DM_D_ERP_ITEM] (NOLOCK) B "
                            + "ON A.ITEMCODE = B.ITEMCODE WHERE A.DIVISION = '{0}'";
                        sql = string.Format(sql, dbName);
                        DataTable stdData = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sql);
                        return stdData.AsEnumerable().AsParallel().Select(dr => new Item()
                        {
                            itemCode = dr.Field<string>("ItemCode").ToStringTrim(),
                            itemRemark = "",
                            supplierCode = "",
                            itemName_ZH = dr.Field<string>("ItemName_ZH").ToStringTrim(),
                            purchasePrice = dr.Field<decimal?>("Price") == null ? 0 : dr.Field<decimal>("Price"),
                            stockUnit = dr.Field<string>("stockUnit").ToStringTrim(),
                            conversionRate = dr.Field<decimal?>("ConversionRate") == null ? 0 : dr.Field<decimal>("ConversionRate"),
                            bomUnit = dr.Field<string>("bomUnit").ToStringTrim(),
                            itemStatus =  "A"
                        }).ToList();
                    }
                }

                sql = " and cast({0} as datetime) >= validfrom {1}";
                if (type == "recipe")
                {
                    // Recipe取最新价格
                    sql = string.Format(sql, "GETDATE()", "");
                } 
                else
                {
                    // Menu 根据日期选取数
                    sql = string.Format(sql, "GETDATE()", "");
                    if (!string.IsNullOrWhiteSpace(requiredDate))
                    {
                        var date = DateTime.Parse(requiredDate);
                        sql = string.Format(sql, string.Format("'{0}'", date), string.Format(" and cast('{0}' as datetime) < (cast(isnull(validto,'2222-12-12') as datetime) + 1)", date));
                    }
                }
                sql = GetSqlItemResource(sql, dbName);
                // DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sql);
                DataTable data = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

                if (data == null && data.Rows.Count == 0)
                   return null;

                return data.AsEnumerable().AsParallel().
                        Select(dr => new Item()
                        {
                            itemGuid = "",
                            itemRemark = "",//itemremark
                            supplierCode = dr.Field<string>("crdnr").ToStringTrim(),
                            supplierName = dr.Field<string>("Supplier_Name").ToStringTrim(),
                            nation = dr.Field<string>("nation").ToStringTrim(),
                            nationGUID = dr.Field<string>("nationGUID").ToStringTrim(),
                            menuClassName = dr.Field<string>("menuClassName").ToStringTrim(),
                            menuClassGUID = dr.Field<string>("menuClassGUID").ToStringTrim(),
                            itemCostperDish = dr.Field<decimal?>("Price").ToNumber<decimal>(),
                            company = dr.Field<string>("Company").ToStringTrim(),
                            itemCode = dr.Field<string>("ItemCode").ToStringTrim(),
                            itemName_ZH = dr.Field<string>("ItemName_ZH").ToStringTrim(),
                            itemName_EN = dr.Field<string>("ItemName_EN").ToStringTrim(),
                            specification = dr.Field<string>("specification").ToStringTrim(),
                            purStk_Conversion = (decimal)dr.Field<double>("purStk_Conversion"),
                            itemType = dr.Field<string>("itemType").ToStringTrim(),
                            purchasePrice = dr.Field<decimal?>("Price") == null ? 0 : dr.Field<decimal>("Price"),
                            tax = dr.Field<decimal?>("tax") == null ? 0 : dr.Field<decimal>("tax"),
                            purchaseUnit = dr.Field<string>("purchaseUnit").ToStringTrim(),
                            conversionRate = dr.Field<decimal?>("ConversionRate") == null ? 0 : dr.Field<decimal>("ConversionRate"),
                            bomUnit = dr.Field<string>("bomUnit").ToStringTrim(),
                            stockUnit = dr.Field<string>("stockUnit").ToStringTrim(),
                            validfrom = dr.Field<string>("validfrom"),
                            validto = dr.Field<string>("validto").ToStringTrim(),
                            itemStatus = string.IsNullOrWhiteSpace(dr.Field<string>("validto"))? "A": ((dr.Field<string>("validto").ToDateTime().ToString("yyyy-MM-dd")).CompareTo(DateTime.Now.ToString("yyyy-MM-dd"))) > 0 ? "A" : "B"
                        }).ToList(); 
            }
            catch(Exception e)
            {
                throw e;
            }
        }
        public string GetSqlItemResource(string sqlfilter, string dbName)
        {
            #region
            //sql = "SELECT distinct a1.crdnr,a2.Supplier_Name,b2.Class02_Code as nationGUID, b2.Class02_Name as nation," +
            //    "b1.Class03_Code as menuClassGUID,b1.Class03_Name as menuClassName, "
            //    + "a1.division as Company, a1.database_name as Site,a1.area,a1.city,a1.region,"
            //    + " cast(a1.price as decimal(18,6)) price,cast(a1.btwper/100 as decimal(8,6)) tax,"
            //    + "a1.ItemCode,a1.Items_Name as ItemName_ZH,a1.ItemEnName as ItemName_EN,a1.Condition_Code,a1.Brand itemBrand, "
            //    + "a1.specification as specification,a1.unitcode,cast(a1.ConversionRate as decimal(18,3)) ConversionRate, a1.ConversionUnit, "
            //    + "Case ltrim(rtrim(a1.ConversionUnit)) when 'KG' then cast((1*a1.ConversionRate)*1000 as decimal(18,0)) " +
            //    "when 'L' then cast((1*a1.ConversionRate)*1000 as decimal(18,0)) else cast((1*a1.ConversionRate) as decimal(18,0)) End Weight, "
            //    + "case left(a1.ItemCode, 5) when '10302' then 'Source' else 'RM' end as itemType," +
            //    "convert(varchar(10),a1.validfrom,23) validfrom,convert(varchar(10),a1.validto,23) validto "
            //    + "FROM (DM_D_ERP_PurchaseAgreement (nolock) a1 join d_item(nolock) a0 on a1.Itemcode = a0.Items_Code) "
            //    + "join d_item_class_03 (nolock) b1 on b1.Class03_Code = a0.class03_code "
            //    + "join d_item_class_02 (nolock) b2 on b2.Class02_Code = b1.Class02_Code "
            //    + "join d_supplier (nolock) a2 on a2.Supplier_Code=a1.supplierCode "
            //    + "where a1.ItemCode like '10%' and a1.Condition_Code='A' and a1.division = '{0}' " + sql
            //    + "order by a1.crdnr,a1.ItemCode";
            #endregion

            //by itemcode, crdnr, 得出一条记录

            //string sql = "WITH A AS (SELECT distinct (ROW_NUMBER() OVER (PARTITION BY A1.ITEMCODE,A1.Supplier_Code ORDER BY VALIDFROM DESC)) AS SORTID," +
            //         "a1.Supplier_Code as crdnr,a2.Supplier_Name,b2.Class02_Code as nationGUID, b2.Class02_Name as nation," +
            //         "b1.Class03_Code as menuClassGUID,b1.Class03_Name as menuClassName, " +
            //         "a1.division as Company, a1.Site,a1.area,a1.city,a1.region," +
            //         " cast(a1.price as decimal(18,6)) price,cast(a1.tax as decimal(8,6)) tax," +
            //         "a1.ItemCode,a1.ItemName_ZH,a1.ItemName_EN,a1.Condition_Code,a1.itemBrand, " +
            //         "a1.specification as specification," +
            //         "a1.Pur_Unit as purchaseUnit, " + 
            //         "cast(isnull(a1.PurStk_Conversion,1)*isnull(StkBom_Conversion,1) as decimal(18,3)) ConversionRate, " +
            //         "a1.PurStk_Conversion, " +
            //         "a1.BOM_Unit as bomUnit, a1.Stock_Unit as stockUnit, " + 
            //         "case left(a1.ItemCode, 5) when '10302' then 'Source' else 'RM' end as itemType," +
            //         "convert(varchar(10),a1.validfrom,23) validfrom,convert(varchar(10),a1.validto,23) validto " +
            //         "FROM ([dbo].[DM_D_ERP_PurchaseAgreement_NEW] (nolock) a1 join d_item(nolock) a0 on a1.Itemcode = a0.Items_Code) " +
            //         "join d_item_class_03 (nolock) b1 on b1.Class03_Code = a0.class03_code " +
            //         "join d_item_class_02 (nolock) b2 on b2.Class02_Code = b1.Class02_Code " +
            //         "join d_supplier (nolock) a2 on a2.Supplier_Code=a1.Supplier_Code " +
            //         "where a1.ItemCode like '10%' and a1.Condition_Code='A' and a1.division = '{0}' " + sqlfilter +
            //         ") SELECT * FROM A WHERE A.SORTID = 1;";

            string sql = "WITH A AS (SELECT distinct (ROW_NUMBER() OVER (PARTITION BY A1.ITEMCODE,A1.Supplier_Code ORDER BY VALIDFROM DESC)) AS SORTID," +
                    "a1.Supplier_Code as crdnr,a2.Supplier_Name,a1.Class02Code as nationGUID, a1.Class02Name as nation," +
                    "a1.Class03Code as menuClassGUID,a1.Class03Name as menuClassName, " +
                    "a1.division as Company," +
                    " cast(a1.price as decimal(18,6)) price,cast(a1.tax as decimal(8,6)) tax," +
                    "a1.ItemCode,a1.ItemName_ZH,a1.ItemName_EN,a1.Condition_Code,a1.itemBrand, " +
                    "a1.specification as specification," +
                    "a1.Pur_Unit as purchaseUnit, " +
                    "cast(isnull(a1.PurStk_Conversion,1)*isnull(StkBom_Conversion,1) as decimal(18,3)) ConversionRate, " +
                    "a1.PurStk_Conversion, " +
                    "a1.BOM_Unit as bomUnit, a1.Stock_Unit as stockUnit, " +
                    "case left(a1.ItemCode, 5) when '10302' then 'Source' else 'RM' end as itemType," +
                    "convert(varchar(10),a1.validfrom,23) validfrom,convert(varchar(10),a1.validto,23) validto " +
                    "FROM ([dbo].[DM_D_ERP_PurchaseAgreement_NEW] (nolock) a1 join DM_D_ERP_ITEM (nolock) a0 on a1.ItemCode=a0.ItemCode) " +
                    "join [dbo].[DM_D_ERP_SUPPLIER] (nolock) a2 on a2.Supplier_Code = a1.Supplier_Code " +
                    "where a1.ItemCode like '10%' and a1.division = '{0}' " + sqlfilter +
                    ") SELECT * FROM A WHERE A.SORTID = 1;";

            sql = string.Format(sql, dbName);
            return sql;
        }
        public List<Item> getPurchaseItems(string dbName, string costCenterCode, string requiredDate, string type, string filterSupplier)
        {
            try
            {

                List<Item> data = getPurchaseItemsFromMDM(dbName,costCenterCode,requiredDate,type);
                
                List<dynamic> lstSuppliers = null;
                var qry = data.Select(q => new { a = q, b = (dynamic)null });
                
                //获取指定Site的价目表，则不加新增原料
                if (!string.IsNullOrWhiteSpace(costCenterCode))
                {
                    try
                    {
                        lstSuppliers = new MastData.SupplierFactory().getSuppliers(dbName, costCenterCode, string.IsNullOrWhiteSpace(requiredDate) ? DateTime.Now : DateTime.Parse(requiredDate));
                    }
                    catch
                    {
                        lstSuppliers = null;
                    }
                    //    if (dbName != "384")
                    //{
                    //    qry = (from a in data
                    //           from b in lstSuppliers
                    //           where a.supplierCode == b.supplierCode &&
                    //                 (a.nationGUID == b.class_Code || a.menuClassGUID == b.class_Code)
                    //           select new { a, b });
                    //}
                    //else
                    //{
                    //    qry = (from a in data
                    //           from b in lstSuppliers
                    //           where a.supplierCode == b.supplierCode
                    //           select new { a, b });
                    //}
                }
                var categories = data.GroupBy(p => new
                {
                    ItemGUID = p.itemGuid,
                    nation = p.nation,
                    nationGUID = p.nationGUID,
                    menuClassName = p.menuClassName,
                    menuClassGUID = p.menuClassGUID,
                    Company = p.company,
                    ItemCode = p.itemCode,
                    ItemName_ZH = p.itemName_ZH,
                    ItemName_EN = p.itemName_EN,
                    specification = p.specification,
                    itemType = p.itemType,
                    ConversionRate = p.conversionRate,
                    bomUnit = p.bomUnit,
                    stockUnit = p.stockUnit,
                    itemRemark = p.itemRemark,
                    purStk_Conversion = p.purStk_Conversion
                })
                .Select(r =>
                {
                    //1 维护菜单时，选择最接近的价目表
                    IEnumerable<Item> suppliers = r.OrderByDescending(q => DateTime.Parse(string.IsNullOrWhiteSpace(q.validto)? "2099-12-31":q.validto)).ThenBy(q => q.supplierCode);
                    var g = suppliers.FirstOrDefault();

                    //3 Site指定供应商
                    if (!string.IsNullOrWhiteSpace(costCenterCode) && lstSuppliers != null)//有明确的成本中心
                    {
                        //多个供应商新消息
                        //先匹配第三级别分类,再匹配第二级别分类
                        //var tmp = lstSuppliers.Where(q => q.class_Code == r.Key.menuClassGUID);
                        var tmp = lstSuppliers.Where(q => (q.class_Code?? "") == r.Key.menuClassGUID);
                        if (!tmp.Any())
                        {
                            // 越南tblDataMapping - RMClassMapping不分类，class_Code为空（null）
                            //tmp = lstSuppliers.Where(q => (string.IsNullOrWhiteSpace(q.class_Code) ? r.Key.nationGUID : q.class_Code) == r.Key.nationGUID);
                            tmp = lstSuppliers.Where(q => (q.class_Code ?? "") == r.Key.nationGUID);
                            if(!tmp.Any())
                                tmp = lstSuppliers.Where(q => string.IsNullOrWhiteSpace(q.class_Code));
                        }
                        
                        //一个item，多个供应商
                        suppliers = r.Where(q => tmp.Any(p => p.supplierCode == q.supplierCode));//.ToList();

                        //r.Where(q => q.b.class_Code == r.Key.menuClassGUID).Select(q => q.a).Distinct(); //匹配第三级别分类
                        //if (!suppliers.Any()) //没有第三级分类，匹配第二级别分类
                        //    suppliers = r.Where(q => q.b.class_Code == r.Key.nationGUID).Select(q => q.a);

                        //取出一个供应商，先考虑二级分类匹配，再考虑默认供应商
                        var tmp1 = tmp.OrderBy(q => q.defaultSupplier ? 0 : 1).FirstOrDefault();
                        if (tmp1!=null)
                        {
                            g = r.Where(q => q.supplierCode == tmp1.supplierCode).Select(q =>
                            {
                                q.directBuy = tmp1.directBuy;
                                q.addToMenu = tmp1.addToMenu;
                                q.defaultSupplier = tmp1.defaultSupplier;
                                q.leadTime = tmp1.leadTime;
                                return q;
                            }).FirstOrDefault();
                        }
                        //g = r.Select(q => {
                        //    //bool check = tmp1 == null;
                        //    bool check = q.supplierCode == tmp1.supplierCode;

                        //    q.directBuy = check? tmp1.directBuy : false;
                        //    q.addToMenu = check ? tmp1.addToMenu : false;
                        //    q.defaultSupplier = check ? tmp1.defaultSupplier : false;
                        //    q.leadTime = check ? tmp1.leadTime : 0;
                        //    return q;
                        //}).FirstOrDefault();
                    }
                    return new Item()
                    {
                        supplierCode = (g == null ? "" : g.supplierCode),
                        supplierName = (g == null ? "" : g.supplierName),
                        defaultSupplier = (g == null ? false : g.defaultSupplier),
                        itemGuid = r.Key.ItemGUID,
                        itemRemark = r.Key.itemRemark,
                        nation = r.Key.nation,
                        nationGUID = r.Key.nationGUID,
                        menuClassName = r.Key.menuClassName,
                        menuClassGUID = r.Key.menuClassGUID,
                        itemCostperDish = (g == null ? 0 : g.itemCostperDish),
                        weight = (g == null ? 0 : g.weight),
                        company = r.Key.Company,
                        itemCode = r.Key.ItemCode,
                        itemName_ZH = r.Key.ItemName_ZH,
                        itemName_EN = r.Key.ItemName_EN,
                        addToMenu = (g == null ? false : g.addToMenu),
                        directBuy = (g == null ? false : g.directBuy),
                        specification = r.Key.specification,
                        purStk_Conversion = r.Key.purStk_Conversion,
                        purchaseUnit = (g == null ? "" : g.purchaseUnit),
                        itemType = r.Key.itemType,
                        purchasePrice = (g == null ? 0 : g.purchasePrice),
                        tax = (g == null ? 0 : g.tax),
                        conversionRate = r.Key.ConversionRate,
                        bomUnit = r.Key.bomUnit,
                        stockUnit = r.Key.stockUnit,
                        validto = string.IsNullOrWhiteSpace((g == null ? "" : g.validto)) ? "2099-12-31" : g.validto,
                        itemStatus = (g == null ? "B" : g.itemStatus),
                        suppliers = string.IsNullOrWhiteSpace(costCenterCode) || g == null ? null : suppliers.ToList(),
                        leadTime = (g == null ? 0 : g.leadTime)
                    };
                }).ToList();
                // 按需判断是否过滤掉没有供应商代码的记录
                if (!string.IsNullOrWhiteSpace(filterSupplier))
                    categories = categories.Where(dr =>!string.IsNullOrWhiteSpace(dr.supplierCode)).ToList();
                if ((string.IsNullOrWhiteSpace(costCenterCode))) return categories;
                //调用Chris的方法，ItemCode, Supplier
                var itemSupplier = new MenuOrder.WeeklyMenuFactory().GetItemSupplierMapping(costCenterCode, requiredDate);
                if (itemSupplier != null)
                {
                    // 匹配
                    categories = categories.GroupJoin(itemSupplier,
                        adj => new { ITEMCODE = adj.itemCode, SUPPLIERCODE = adj.supplierCode },
                        tmp => new { ITEMCODE = tmp.itemCode, SUPPLIERCODE = tmp.supplierCode },
                        (adj, tmp) =>
                        {
                            bool check = tmp == null || tmp.Count() == 0;                        
                            // supplierCode
                            adj.supplierCode = check ? adj.supplierCode : tmp.FirstOrDefault().supplierCode;
                            // supplierName
                            adj.supplierName = check ? adj.supplierName : (string.IsNullOrWhiteSpace(tmp.FirstOrDefault().supplierName) && !string.IsNullOrWhiteSpace(tmp.FirstOrDefault().supplierCode) ? adj.supplierName : tmp.FirstOrDefault().supplierName);
                            // conversionUnit
                            adj.conversionUnit = check ? adj.conversionUnit : tmp.FirstOrDefault().conversionUnit;
                            // conversionRate
                            adj.conversionRate = check ? adj.conversionRate.ToDecimal() : tmp.FirstOrDefault().conversionRate.ToDecimal();
                            // purchasePrice
                            adj.purchasePrice = check ? adj.purchasePrice.ToDecimal() : tmp.FirstOrDefault().purchasePrice.ToDecimal();
                            // tax
                            adj.tax = check ? adj.tax.ToDecimal() : tmp.FirstOrDefault().purchaseTax.ToDecimal();
                            // purchaseUnit
                            adj.purchaseUnit = check ? adj.purchaseUnit.ToString() : tmp.FirstOrDefault().purchaseUnit.ToString();

                            return adj;
                        }).ToList();
                }
                return categories;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        #endregion

        #region 得到一个公司当前有效的所有采购物料分类
        //purchaseType: addToMenu(加菜时可以选择的采购物料)
        public List<ItemClass> getItemClass(string companyCode, string costCenterCode, string date, string purchaseType = null)
        {
            try
            {
                //可以添加到菜单的产品大类

                List<dynamic> lstItemClass = null;
                if(purchaseType.ToStringTrim().ToLower() == "addtomenu")
                    lstItemClass = (new MastData.SupplierFactory()).getSuppliers(companyCode, costCenterCode, DateTime.Parse(date))
                        .Where(q => q.addToMenu).ToList();

                List<Item> list = getPurchaseItems(companyCode, costCenterCode, date,null,null);
                if (list == null || !list.Any()) return null;
                //获取最外层Menu, 即所有父GUID为空

                // = list.OrderBy(q => q.nationGUID).ThenBy(q => q.menuClassGUID).Select(q=>q);
                if (lstItemClass != null)
                    list = (from d in list
                            from a in lstItemClass
                            where d.supplierCode == a.supplierCode &&
                                (d.nationGUID == a.class_Code || d.menuClassGUID == a.class_Code)
                            select d).ToList();

                /*
                 * 
                    (from d in list.OrderBy(q => q.nationGUID).ThenBy(q => q.menuClassGUID)//dt.AsEnumerable()
                    from a in lstItemClass
                     where d.supplierCode == a.supplierCode &&
                       (d.nationGUID == a.class_Code || d.menuClassGUID == a.class_Code)
                       */
               

                return getItemClass(list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 得到一个所有采购物料分类
        //purchaseType: addToMenu(加菜时可以选择的采购物料)
        public List<ItemClass> getItemClass(string companyCode)
        {
            string sql = "select distinct a3.Class02_Code,a4.Class02_Name, a3.Class03_Code,a3.Class03_Name from " +
               // "from (SELECT distinct a2.class03_code  " +
               // "FROM DM_D_ERP_PurchaseAgreement a1,d_item a2 " +
               // "WHERE a1.itemcode LIKE '10%' AND a1.validfrom <= getdate() AND a1.division = '{0}' AND a1.itemcode = a2.Items_Code) a1," +
                "d_item_class_03 a3,d_item_class_02 a4 where " +
               // "WHERE a1.Class03_Code = a3.Class03_Code " +
                "a3.class01_code='10' "+
                "and a3.Class02_Code = a4.Class02_Code " +
                "order by a3.Class02_Code,a3.Class03_Code";

            sql = string.Format(sql, companyCode);
            var data = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sql).AsEnumerable().
                Select(dr => new Item()
                {
                    nation = dr.Field<string>("Class02_Name").ToStringTrim(),
                    nationGUID = dr.Field<string>("Class02_Code").ToStringTrim(),
                    menuClassName = dr.Field<string>("Class03_Name").ToStringTrim(),
                    menuClassGUID = dr.Field<string>("Class03_Code").ToStringTrim(),

                }).ToList();
            return getItemClass(data);
         }
          #endregion

        private List<ItemClass> getItemClass(List<Item> list)
        {
            try
            {
                int index = 1;
                var data2 = from d in list.OrderBy(q => q.nationGUID).ThenBy(q => q.menuClassGUID)
                            group d by new
                            {
                                value = d.nationGUID,//d.Field<string>("value2").ToStringTrim(),
                                label = d.nation,//d.Field<string>("label2").ToStringTrim(),
                            } into g
                            select new ItemClass()
                            {
                                itemCodeId = index++.ToString(),
                                value = g.Key.value,
                                label = g.Key.label,
                                disabled = "",
                                children = g.Select(q => new { value = q.menuClassGUID, label = q.menuClassName })
                                 .Distinct().Select(q =>  //GetnewChildMenus(g)
                                {
                                     int x = 0;

                                     return new ItemClass()
                                     {
                                         itemCodeId = x++.ToString(),
                                         value = q.value,//dr.Field<string>("value3").ToStringTrim(),
                                        label = q.label//dr.Field<string>("label3").ToStringTrim()
                                    };
                                 }).ToList(),
                            };

                return data2.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
